const connection = require('../app/databases');

class MomentService {
  async create(content, id) {
    try {
      const statement = ` INSERT INTO moment (content, user_id) VALUES (?, ?);`;
      const [result] = await connection.execute(statement, [content, id]);
      
      return result;
    } catch (error) {
      return error.message;
    }
  }

  async getMoment(momentId) {
    try {
      const statement = `
        SELECT 
          m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
          JSON_OBJECT('id', u.id, 'name', u.name, 'avatar', u.avatar_url) avatar,
          JSON_ARRAYAGG(
            JSON_OBJECT(
              'id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt, 'updateTime', c.updateAt,
              'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatar', cu.avatar_url)
            )
          ) comments
        FROM moment m
        LEFT JOIN user u ON u.id = m.user_id
        LEFT JOIN comment c ON c.moment_id = m.id
        LEFT JOIN user cu ON cu.id = c.user_id
        WHERE m.id = ?;
      `;
      const [result] = await connection.execute(statement, [momentId]);

      return result;
    } catch (error) {
      return error.message;
    }
  }

  async getMoments(offset, size) {
    try {
      const statement = `
        SELECT
          m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
          JSON_OBJECT('id', u.id, 'name', u.name, 'avatar', u.avatar_url) author,
          (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount
        FROM moment m
        LEFT JOIN user u ON m.user_id = u.id
        LIMIT ?, ?;
      `;
      const [result] = await connection.execute(statement, [offset, size]);

      return result;
    } catch (error) {
      return error.message;
    }
  }

  async changeMoment(momentId, content) {
    try {
      const statement = `UPDATE moment SET content = ? WHERE id = ?`;
      const [result] = await connection.execute(statement, [content, momentId]);

      return result;
    } catch (error) {
      return error.message;
    }
  }

  async removeMoment(momentId) {
    try {
      const statement = `DELETE FROM moment WHERE id = ?`;
      const [result] = await connection.execute(statement, [momentId]);
      
      return result;
    } catch (error) {
      return error.message;
    }
  }
}

module.exports = new MomentService();